# https://www.bilibili.com/video/BV1W7411G7wP?p=16&spm_id_from=pageDriver&vd_source=8bd7b24b38e3e12c558d839b352b32f4
# =IFNA(VLOOKUP(A2,Scores!A2:B21,2,FALSE),0)
import pandas as pd
import matplotlib.pyplot as plt

print('===============================================================')
# 使用pandas指定了ID的情况下
students_with_id = pd.read_excel('../resources/input_py_tools/016/Student_Score.xlsx', sheet_name='Students', index_col='ID')
scores_with_id = pd.read_excel('../resources/input_py_tools/016/Student_Score.xlsx', sheet_name='Scores', index_col=0) # 看来新版本推荐使用索引
print(students_with_id)
print(scores_with_id)

# 内连接
table_inner_02 = students_with_id.merge(scores_with_id, left_on=students_with_id.index, right_on=scores_with_id.index)
print(table_inner_02)
# 左连接
table_left_02 = students_with_id.merge(scores_with_id, left_on=students_with_id.index, right_on=scores_with_id.index, how='left').fillna(0)
#table_left_02['Score'] = table_left_02['Score'].astype(int)
table_left_02.Score = table_left_02.Score.astype(int)
print(table_left_02)